﻿// Copyright Microsoft

using System;
using System.Windows;
using System.Activities.Presentation.View;
using System.Activities.Presentation;
using System.Collections.Generic;
using System.Windows.Controls;
using System.Windows.Controls.Primitives;
using System.Windows.Media;
using System.Data.SqlClient;
using System.Activities.Presentation.Model;
using Microsoft.Samples.SqlServer.ExtensionMethods;
using System.Activities.Statements;
using System.Collections.ObjectModel;
using System.Linq;
using System.Data;
using System.Windows.Input;
using System.Activities;

namespace Microsoft.Samples.SqlServer.Activities.Designers
{
  // Interaction logic for SelectDataTableDesigner.xaml
  public partial class SelectDataTableDesigner
  {
        public SqlConnectionStringBuilder SqlConnectionStringBuilder { get; set; }

        public SelectDataTableDesigner()
        {
            InitializeComponent();
        }

        private void ActivityDesigner_Loaded(object sender, RoutedEventArgs e)
        {
            //NOTE: The sample assumes a Sequence ConnectionBuilder variable as SqlConnectionBuilder
            Sequence rootSequence = this.ModelItem.RootSequence() as Sequence;
            if (rootSequence != null)
            {

                this.ModelItem.Properties["SqlConnectionBuilder"].SetValue(new InArgument<SqlConnectionStringBuilder>(
                    (from v in rootSequence.Variables
                where v.Name == "ConnectionStringBuilder"
                select v).First()));
            }
        }
        
        private void tableNameComboBox_DropDownOpened(object sender, EventArgs e)
        {
            ComboBox tableNameComboBox = (sender as ComboBox);
            
            if (this.SqlConnectionStringBuilder != null)
            {
                //Assume Sequence activity with ConnectionBuilderActivity
                ModelItem sequence = this.ModelItem.GetParent(typeof(Sequence));

                //Get ConnectionBuilderActivity. Only uses the first one found
                var connectionBuilderActivity =
                    (from a in sequence.Properties["Activities"].ComputedValue as Collection<System.Activities.Activity>
                    where a.GetType() == typeof(ConnectionBuilderActivity)
                    select a).First() as ConnectionBuilderActivity;

                this.SqlConnectionStringBuilder.DataSource = connectionBuilderActivity.DataSource.Expression.ToString();
                this.SqlConnectionStringBuilder.InitialCatalog = connectionBuilderActivity.InitialCatalog.Expression.ToString();
            }

            using (SqlConnection conn = new SqlConnection(this.SqlConnectionStringBuilder.ToString()))
            {
                conn.Open();

                string schemaName = this.ModelItem.Properties["Schema"].ComputedValue.ToString();

                List<string> tables = (from r in conn.GetSchema("TABLES").AsEnumerable()
                    where r.Field<string>("table_schema") == schemaName && r.Field<string>("TABLE_TYPE") != "VIEW"
                    orderby r.Field<string>("table_schema")
                    select String.Format("{0}.{1}", r.Field<string>("table_schema"), r.Field<string>("TABLE_NAME"))).ToList<string>();

                tableNameComboBox.ItemsSource = tables;
            }

        }

        private void tableNameComboBox_DropDownClosed(object sender, EventArgs e)
        {
            this.ModelItem.Properties["TableSource"].SetValue((sender as ComboBox).Text);
        }
        
        private void schemaNameComboBox_DropDownOpened(object sender, EventArgs e)
        {
            ComboBox schemaNameComboBox = (sender as ComboBox);

            using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
            {
                conn.Open();

                var schema = from s in
                     ((from r in conn.GetSchema("TABLES").AsEnumerable()
                        select r.Field<string>("table_schema")).ToList<string>())
                        group s by s into schemaGroup
                        orderby schemaGroup.Key
                        select schemaGroup;

                schemaNameComboBox.Items.Clear();
                foreach (var item in schema)
                {
                    schemaNameComboBox.Items.Add(item.Key);
                }
                
            }
        }
        
        private void schemaNameComboBox_DropDownClosed(object sender, EventArgs e)
        {
            this.ModelItem.Properties["Schema"].SetValue((sender as ComboBox).Text);
        }

        private string ConnectionString()
        {
            if (this.SqlConnectionStringBuilder != null)
            {
                //Assume Sequence activity with ConnectionBuilderActivity
                ModelItem sequence = this.ModelItem.GetParent(typeof(Sequence));

                //Get ConnectionBuilderActivity. Only uses the first one found
                var connectionBuilderActivity =
                    (from a in sequence.Properties["Activities"].ComputedValue as Collection<System.Activities.Activity>
                     where a.GetType() == typeof(ConnectionBuilderActivity)
                     select a).First() as ConnectionBuilderActivity;

                this.SqlConnectionStringBuilder.DataSource = connectionBuilderActivity.DataSource.Expression.ToString();
                this.SqlConnectionStringBuilder.InitialCatalog = connectionBuilderActivity.InitialCatalog.Expression.ToString();
            }

            return this.SqlConnectionStringBuilder.ToString();
        }

        //NEXT: Use ComboBox.ItemTemplate binding
        private void columnNamesComboBox_DropDownOpened(object sender, EventArgs e)
        {
            ComboBox columnNamesComboBox = (sender as ComboBox);

            string selectItems = string.Empty;
            if (this.ModelItem.Properties["ColumnNames"].ComputedValue != null)
                selectItems = this.ModelItem.Properties["ColumnNames"].ComputedValue.ToString();
            List<string> columnNameList = (from i in selectItems.Split(new char[] { ',' }) select i).ToList<string>();

            Mouse.SetCursor(Cursors.Wait);
            
            using (SqlConnection conn = new SqlConnection(this.ConnectionString()))
            {
                conn.Open();

                string tableName = this.ModelItem.Properties["TableSource"].ComputedValue.ToString();

                string[] splitName = tableName.Split(new char[] { '.' });
                string nonSchemaName = splitName.Length > 0 ? splitName[1] : string.Empty;

                List<Column> schema = (from r in conn.GetSchema("Columns").AsEnumerable()
                                           where r.Field<string>("TABLE_NAME") == nonSchemaName
                                           orderby r.Field<int>("ORDINAL_POSITION")
                                           select new Column
                                           {
                                               SchemaName = r.Field<string>("table_schema"),
                                               Name = r.Field<string>("COLUMN_NAME"),
                                               DataType = r.Field<string>("DATA_TYPE"),
                                               DataSize = r.Field<int?>("CHARACTER_MAXIMUM_LENGTH"),
                                               IsNullable = r.Field<string>("IS_NULLABLE") == "YES" ? true : false
                                           }
                                           ).ToList<Column>();

                //NEXT: Data Binding: columnNamesComboBox.ItemsSource
                columnNamesComboBox.Items.Clear();

                foreach (Column c in schema)
                {

                    Grid comboBoxGrid = new Grid();

                    // Define the Columns
                    ColumnDefinition colDef1 = new ColumnDefinition();
                    comboBoxGrid.ColumnDefinitions.Add(colDef1);

                    // Define the Rows
                    RowDefinition rowDef1 = new RowDefinition();

                    comboBoxGrid.RowDefinitions.Add(rowDef1);

                    CheckBox selectCheckbox = new CheckBox
                    {
                        IsChecked = columnNameList.Contains(string.Format("[{0}]", c.Name)),
                        Content = string.Format("[{0}]", c.Name),
                        VerticalAlignment = System.Windows.VerticalAlignment.Center,
                        VerticalContentAlignment = System.Windows.VerticalAlignment.Center,
                        Margin = new Thickness(3, 0, 0, 0)
                    };

                    Grid.SetRow(selectCheckbox, 0);
                    Grid.SetColumn(selectCheckbox, 1);

                    comboBoxGrid.Children.Add(selectCheckbox);

                    columnNamesComboBox.Items.Add(comboBoxGrid);
                }

                Mouse.SetCursor(Cursors.Arrow);
            }
        }
        
        private void columnNamesComboBox_DropDownClosed(object sender, EventArgs e)
        {
            ComboBox selectComboBox = (sender as ComboBox);
            string selectItems = string.Empty;

            foreach (Grid item in selectComboBox.Items)
            {   
                CheckBox selectCheckBox = item.Children[0] as CheckBox;

                if ((bool)selectCheckBox.IsChecked)
                {
                    selectItems += string.Format("{0},", selectCheckBox.Content.ToString());
                }
            }

            if (selectItems.Length > 0)
            {
                //Remove last comma
                selectItems = selectItems.Substring(0, selectItems.Length - 1);
            }

            this.ModelItem.Properties["ColumnNames"].SetValue(selectItems);
        }

  }

  public class SqlAzureSecurity
  {
      public bool Encrypt { get; set; }
      public bool TrustServerCertificate { get; set; }
  }

  public abstract class Schema
  {
      public string SchemaName { get; set; }
      public string Name { get; set; }
      public string DataType { get; set; }
      public int? DataSize { get; set; }
  }

  public class Column : Schema
  {
      public bool IsNullable { get; set; }
  }

  public class Parameter : Schema
  {
      public string Mode { get; set; }
  }
}
